import sqlite3
import requests
from flask import Flask, render_template
from flask import *
import socket
import pymysql
app = Flask(__name__)
error_user_pass = ""
error_register = ""
error_register_password = ""
home_login_test = False


# db=pymysql.connect(
#     host='localhost',
#     user='root',
#     password='123456',
#     database='maoxuechunqimozuoye'
# )
# py_cursor=db.cursor()
@app.route("/")  # 登录界面
def login():
    return render_template('login.html',error_user_pass=error_user_pass)

@app.route("/login_check", methods=['POST'])  # 登录验证
def login_check():
    global error_user_pass
    global right_username
    global home_login_test
    home_login_test = False  # 防止不登录跳转主页面
    right_username = ""
    error_user_pass = ""
    # conn = sqlite3.connect("user_information.db")
    # cur = conn.cursor()
    username = request.form['Username']
    password = request.form['Password']
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * FROM brian_user_information WHERE username = '{username}'")
    exit_name=py_cursor.fetchone()
    if exit_name is None:  # 用户不存在
        error_user_pass = "用户或密码不存在"
        db.commit()
        db.close()
        return redirect("/")
    else:
        if password != exit_name[1]:
            error_user_pass = "用户或密码不存在"
            db.commit()
            db.close()
            return redirect("/")
        else:
            right_username = username
            home_login_test = True
            db.commit()
            db.close()
            return redirect("/home")
        # conn.commit()
        # conn.close()



@app.route("/register")  # 注册
def register():
    return render_template('register.html', error=error_register, error_register_password=error_register_password)

@app.route("/register_check", methods=['POST'])  # 注册验证
def register_check():
    global error_register
    global error_register_password
    error_register = ""
    error_register_password = ""
    # conn = sqlite3.connect("user_information.db")
    # cur = conn.cursor()
    # cur.execute('''CREATE TABLE IF NOT EXISTS brian_user_information(
    # username  TEXT,
    # password   TEXT);''')
    # conn.commit()
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute('''CREATE TABLE IF NOT EXISTS brian_user_information(
        username  VARCHAR(255),
        password   VARCHAR(255));''')
    db.commit()
    username = request.form['Username']
    password = request.form['Password']
    confirm_password = request.form['Confirm_Password']
    # exit_name = cur.execute(f"SELECT * FROM brian_user_information WHERE username = '{username}'").fetchone()
    py_cursor.execute(f"SELECT * FROM brian_user_information WHERE username = '{username}'")
    exit_name=py_cursor.fetchone()
    if exit_name is not None:
        error_register = "用户名已注册"
        return redirect("/register")
    else:
        if password != confirm_password:
            error_register_password = "两次密码不一致，请重新注册"
            return redirect("/register")
        else:
            # cur.execute(f"INSERT INTO brian_user_information(username,password) values('{username}','{password}')")
            py_cursor.execute(f"INSERT INTO brian_user_information(username,password) values('{username}','{password}')")
    # conn.commit()
    # conn.close()
    db.commit()
    db.close()

    return redirect("/")

@app.route("/home_background")  #背景
def home_background():
    return render_template('home_background.html')

@app.route("/modify_password")  # 修改密码的页面
def modify_password():
    return render_template('modify_password.html')

@app.route("/modify_passwd_form", methods=['POST'])  # 修改密码
def modify_passwd_form():
    # conn = sqlite3.connect("user_information.db")
    # cur = conn.cursor()
    # conn.commit()
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    passwd = request.form['passwd']
    # cur.execute(f"update brian_user_information set password='{passwd}'WHERE username = '{right_username}'")
    py_cursor.execute(f"update brian_user_information set password='{passwd}'WHERE username = '{right_username}'")
    # conn.commit()
    # conn.close()
    db.commit()
    db.close()
    return redirect("/modify_password")


@app.route("/home")  # 首页
def home():
    return render_template('home.html')

@app.route("/log_web")  # 日志-Web页面
def log_web():
    return render_template('log_web.html')

@app.route("/log_web/<string:query_IP>")  # 日志-web页面-tables
def log_web_tables(query_IP):
    ret_data = []
    list_IP = []
    list_time = []
    list_URL = []
    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from log_web_information")
    # conn.commit()

    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from log_web_information")
    all=py_cursor.fetchall()
    db.commit()

    if query_IP == "ALL":
        for it in all:
            list_IP.append(it[0])
            list_time.append(it[1])
            list_URL.append(it[2])
    else:
        for it in all:
            if query_IP == it[0]:
                list_IP.append(it[0])
                list_time.append(it[1])
                list_URL.append(it[2])

    db.close()
    n = len(list_URL)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_IP[i - 1],
            "list2":list_time[i-1],
            "list3": list_URL[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }


@app.route("/warning_web")  # 告警-Web页面
def warning_web():
    return render_template('warning_web.html')

@app.route("/warning_web/<string:attack_type>")  # 告警-web页面-tables
def warning_web_tables(attack_type):
    ret_data = []
    list_type = []
    list_IP = []
    list_URL = []
    list_time = []
    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from warning_web_information")
    # conn.commit()

    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from warning_web_information")
    all=py_cursor.fetchall()
    db.commit()
    if attack_type.strip() == "ALL":
        for it in all:
            list_type.append(it[0])
            list_IP.append(it[1])
            list_time.append(it[2])
            list_URL.append(it[3])
    elif attack_type.strip() == "XSS":
        for it in all:
            if it[0] == "XSS":
                list_type.append(it[0])
                list_IP.append(it[1])
                list_time.append(it[2])
                list_URL.append(it[3])
    elif attack_type.strip() == "SQL":
        for it in all:
            if it[0] == "SQL注入":
                list_type.append(it[0])
                list_IP.append(it[1])
                list_time.append(it[2])
                list_URL.append(it[3])
    elif attack_type.strip() == "BruteLog":
        for it in all:
            if it[0] == "web登录暴力破解":
                list_type.append(it[0])
                list_IP.append(it[1])
                list_time.append(it[2])
                list_URL.append(it[3])
    db.close()
    n = len(list_URL)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_type[i - 1],
            "list2": list_IP[i - 1],
            "list3": list_time[i - 1],
            "list4": list_URL[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }

@app.route("/log_pcap")  # 日志-Web页面
def log_pcap():
    return render_template('log_pcap.html')

@app.route("/log_pcap/<string:pro_type>")  # 日志-web页面-tables
def log_pcap_tables(pro_type):
    ret_data = []
    list_dst = []
    list_dport = []
    list_src = []
    list_sport = []
    list_proto = []
    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from log_pcap_information")
    # conn.commit()

    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from log_pcap_information")
    all=py_cursor.fetchall()
    db.commit()

    if pro_type == "ALL":
        for it in all:
            list_dst.append(it[0])
            list_dport.append(it[1])
            list_src.append(it[2])
            list_sport.append(it[3])
            list_proto.append(it[4])
    else:
        for it in all:
            if pro_type == it[4]:
                list_dst.append(it[0])
                list_dport.append(it[1])
                list_src.append(it[2])
                list_sport.append(it[3])
                list_proto.append(it[4])

    db.close()
    n = len(list_dst)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_dst[i - 1],
            "list2": list_dport[i - 1],
            "list3": list_src[i - 1],
            "list4": list_sport[i - 1],
            "list5": list_proto[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }


@app.route("/warning_pcap")  # 告警-pcap页面
def warning_pcap():
    return render_template('warning_pcap.html')

@app.route("/warning_pcap/<string:attack_type>")  # 告警-pcap页面-tables
def warning_pcap_tables(attack_type):
    ret_data = []
    list_type = []
    list_dst = []
    list_dport = []
    list_src = []
    list_sport = []
    list_proto = []
    list_chara = []
    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from warning_pcap_information")
    # conn.commit()

    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from warning_pcap_information")
    all=py_cursor.fetchall()
    db.commit()

    if attack_type.strip() == "ALL":
        for it in all:
            list_type.append(it[0])
            list_dst.append(it[1])
            list_dport.append(it[2])
            list_src.append(it[3])
            list_sport.append(it[4])
            list_proto.append(it[5])
            list_chara.append(it[6])
    elif attack_type.strip() == "XSS":
        for it in all:
            if it[0] == "XSS":
                list_type.append(it[0])
                list_dst.append(it[1])
                list_dport.append(it[2])
                list_src.append(it[3])
                list_sport.append(it[4])
                list_proto.append(it[5])
                list_chara.append(it[6])
    elif attack_type.strip() == "SQL":
        for it in all:
            if it[0] == "SQL注入":
                list_type.append(it[0])
                list_dst.append(it[1])
                list_dport.append(it[2])
                list_src.append(it[3])
                list_sport.append(it[4])
                list_proto.append(it[5])
                list_chara.append(it[6])
    elif attack_type.strip() == "挖矿病毒":
        for it in all:
            if it[0] == "挖矿病毒":
                list_type.append(it[0])
                list_dst.append(it[1])
                list_dport.append(it[2])
                list_src.append(it[3])
                list_sport.append(it[4])
                list_proto.append(it[5])
                list_chara.append(it[6])
    elif attack_type.strip() == "勒索软件":
        for it in all:
            if it[0] == "勒索软件":
                list_type.append(it[0])
                list_dst.append(it[1])
                list_dport.append(it[2])
                list_src.append(it[3])
                list_sport.append(it[4])
                list_proto.append(it[5])
                list_chara.append(it[6])
    elif attack_type.strip() == "冰蝎":
        for it in all:
            if it[0] == "冰蝎":
                list_type.append(it[0])
                list_dst.append(it[1])
                list_dport.append(it[2])
                list_src.append(it[3])
                list_sport.append(it[4])
                list_proto.append(it[5])
                list_chara.append(it[6])
    elif attack_type.strip() == "后门上传":
        for it in all:
            if it[0] == "后门上传":
                list_type.append(it[0])
                list_dst.append(it[1])
                list_dport.append(it[2])
                list_src.append(it[3])
                list_sport.append(it[4])
                list_proto.append(it[5])
                list_chara.append(it[6])
    db.close()
    n = len(list_type)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_type[i - 1],
            "list2": list_dst[i - 1],
            "list3": list_dport[i - 1],
            "list4": list_src[i - 1],
            "list5": list_sport[i - 1],
            "list6": list_proto[i - 1],
            "list7": list_chara[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }


@app.route("/log_Linux")  # 日志-Linux页面
def log_Linux():
    return render_template('log_Linux.html')

@app.route("/log_Linux/<string:quert_time>")  # 日志-Linux页面-tables
def log_Linux_tables(quert_time):
    ret_data = []
    list_time = []
    list_data = []
    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from log_Linux_information")
    # conn.commit()
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from log_Linux_information")
    all=py_cursor.fetchall()
    db.commit()
    if quert_time == "ALL":
        for it in all:
            list_time.append(it[0])
            list_data.append(it[1])


    else:
        for it in all:
            if quert_time in it[0]:
                list_time.append(it[0])
                list_data.append(it[1])


    db.close()
    n = len(list_time)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_time[i - 1],
            "list2": list_data[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }

@app.route("/warning_Linux")  # 告警-Linux页面
def warning_Linux():
    return render_template('warning_Linux.html')

@app.route("/warning_Linux/<string:attack_IP>")  # 告警-Linux页面-tables
def warning_Linux_tables(attack_IP):
    ret_data = []
    list_type = []
    list_time = []
    list_IP = []
    list_data = []

    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from warning_Linux_information")
    # conn.commit()
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from warning_Linux_information")
    all=py_cursor.fetchall()
    db.commit()
    if attack_IP.strip() == "ALL":
        for it in all:
            list_type.append(it[0])
            list_time.append(it[1])
            list_IP.append(it[2])
            list_data.append(it[3])

    else:
        for it in all:
            if it[2] == attack_IP:
                list_type.append(it[0])
                list_time.append(it[1])
                list_IP.append(it[2])
                list_data.append(it[3])

    db.close()
    n = len(list_time)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_type[i - 1],
            "list2": list_time[i - 1],
            "list3": list_IP[i - 1],
            "list4": list_data[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }

@app.route("/log_Windows")  # 日志-Windows页面
def log_Windows():
    return render_template('log_Windows.html')

@app.route("/log_Windows/<string:time_query>")  # 日志-Windows页面-tables
def log_Windows_tables(time_query):
    ret_data = []
    list_time = []
    list_status = []
    list_level = []
    list_computer = []
    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from log_Windows_information")
    # conn.commit()

    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from log_Windows_information")
    all = py_cursor.fetchall()
    db.commit()

    if time_query == "ALL":
        for it in all:
            list_time.append(it[0])
            list_status.append(it[1])
            list_level.append(it[2])
            list_computer.append(it[3])
    else:
        for it in all:
            if time_query in it[0]:
                list_time.append(it[0])
                list_status.append(it[1])
                list_level.append(it[2])
                list_computer.append(it[3])


    db.close()
    n = len(list_time)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_time[i - 1],
            "list2": list_status[i - 1],
            "list3": list_level[i - 1],
            "list4": list_computer[i - 1]
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }

@app.route("/warning_Windows")  # 告警-Windows页面
def warning_Windows():
    return render_template('warning_Windows.html')

@app.route("/warning_Windows/<string:attack_type>")  # 告警-Windows-tables
def warning_Windows_tables(attack_type):
    ret_data = []
    list_time = []
    list_type = []
    list_level = []

    # conn = sqlite3.connect("user_information.db")
    # cursor = conn.cursor()
    # all = cursor.execute(f"SELECT * from warning_Windows_information")
    # conn.commit()
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='maoxuechunqimozuoye'
    )
    py_cursor = db.cursor()
    py_cursor.execute(f"SELECT * from warning_Windows_information")
    all = py_cursor.fetchall()
    db.commit()
    if attack_type.strip() == "ALL":
        for it in all:
            list_time.append(it[0])
            list_type.append(it[1])
            list_level.append(it[2])

    else:
        for it in all:
            if attack_type in it[1]:
                list_time.append(it[0])
                list_type.append(it[1])
                list_level.append(it[2])

    db.close()
    n = len(list_type)
    for i in range(1, n + 1):
        ret_data.append({
            "list1": list_time[i - 1],
            "list2": list_type[i - 1],
            "list3": list_level[i - 1],
        })

    return {
        "code": 0,
        "msg": "",
        "count": n,
        "data": ret_data
    }

if __name__ == '__main__':
    app.run()
